package com.opennews.openplatform.service;

import com.opennews.openplatform.common.constant.IncomeExpenseTypeConstant;
import com.opennews.openplatform.common.constant.PaymentTypeConstant;
import com.opennews.openplatform.myspringbootcore.util.DateHelper;
import com.opennews.openplatform.myspringbootcore.util.SharedUtil;
import lombok.RequiredArgsConstructor;
import org.jooq.DSLContext;
import org.jooq.SelectConditionStep;
import org.jooq.impl.DSL;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

import static com.opennews.openplatform.entity.Tables.*;

@RequiredArgsConstructor
@Service
public class IncomeExpenseAnalysisService {
    private final DSLContext dslContext;
    private final BankService bankService;
    private final IncomeExpenseDetailTypeService incomeExpenseDetailTypeService;

    /**
     * Queries income expense summary for one month, including total income, total expense, and balance.
     *
     * @param accountGroupId the ID of the user's account group
     * @param uniqueDates    a set of dates to query
     * @return a list of maps containing income and expense summaries per date
     */
    public List<Map<String, Object>> queryMonthlySummaryList(String accountGroupId, Set<LocalDateTime> uniqueDates) {
        List<Map<String, Object>> summaryList = new ArrayList<>();

        for (LocalDateTime date : uniqueDates) {
            List<Map<String, Object>> result = dslContext.select(
                            DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).as("amount"),
                            INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.as("paymentType"),
                            INCOME_EXPENSE_DETAIL.TYPE.as("type"))
                    .from(INCOME_EXPENSE_DETAIL)
                    .where(
                            INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(date.getYear()))
                                    .and(DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(date.getMonthValue()))
                    )
                    .groupBy(
                            INCOME_EXPENSE_DETAIL.PAYMENT_TYPE,
                            INCOME_EXPENSE_DETAIL.TYPE
                    )
                    .fetchMaps();

            BigDecimal cashIncome = calculateSummary(result, PaymentTypeConstant.CASH, IncomeExpenseTypeConstant.INCOME);
            BigDecimal cashExpense = calculateSummary(result, PaymentTypeConstant.CASH, IncomeExpenseTypeConstant.EXPENSE);
            BigDecimal creditCardIncome = calculateSummary(result, PaymentTypeConstant.CREDIT_CARD, IncomeExpenseTypeConstant.INCOME);
            BigDecimal creditCardExpense = calculateSummary(result, PaymentTypeConstant.CREDIT_CARD, IncomeExpenseTypeConstant.EXPENSE);

            // Create the summary map for the date
            summaryList.add(Map.of(
                    "incomeExpenseDate", date,
                    "cashIncome", cashIncome,
                    "cashExpense", cashExpense,
                    "cashBalance", cashIncome.add(cashExpense),
                    "creditCardIncome", creditCardIncome,
                    "creditCardExpense", creditCardExpense,
                    "creditCardBalance", creditCardIncome.add(creditCardExpense)
            ));
        }

        return summaryList;
    }

    /**
     * Queries income expense summary based one single day. Includes total income, total expense and balance.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @param dates:          The date list which stores each incomeExpenseDate might duplicate.
     * @return The list of map instance which contains keys below.
     * incomeExpenseDate: The date of income or expense happens. No duplicate date in this list.
     * totalIncome: Float of the total income in this day.
     * totalExpense: Float of the total expense in this day.
     * balance: Float of the balance in this day.
     */
    public List<Map<String, Object>> queryMonthlySummaryList(String accountGroupId, List<LocalDateTime> dates) {
        // Get unique dates from the list, with each date converted to the first day of its month.
        Set<LocalDateTime> uniqueDates = dates.stream()
                .map(DateHelper::getFirstDayOfMonth)
                .collect(Collectors.toSet());

        // Calls the overloaded method with the unique dates
        return queryMonthlySummaryList(accountGroupId, uniqueDates);
    }

    /**
     * Queries income expense summary based one single day. Includes total income, total expense and balance.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @param uniqueDates:    Stores each incomeExpenseDate without duplicate.
     * @return The list of map instance which contains keys below.
     * incomeExpenseDate: The date of income or expense happens. No duplicate date in this list.
     * cashIncome: Float of the total cash income in this day.
     * cashExpense: Float of the total cash expense in this day.
     * cashBalance: Float of the total cash balance in this day.
     * creditCardIncome: Float of the total credit card income in this day.
     * creditCardExpense: Float of the total credit card expense in this day.
     * creditCardBalance: Float of the total credit card balance in this day.
     */
    public List<Map<String, Object>> queryDailySummaryList(String accountGroupId, Set<LocalDateTime> uniqueDates) {
        List<Map<String, Object>> result = new ArrayList<>();

        if (uniqueDates != null && !uniqueDates.isEmpty()) {
            List<Map<String, Object>> rawData = dslContext.select(
                            INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.as("incomeExpenseDate"),
                            DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).as("amount").as("amount"),
                            INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.as("paymentType"),
                            INCOME_EXPENSE_DETAIL.TYPE.as("type")
                    )
                    .from(INCOME_EXPENSE_DETAIL)
                    .where(
                            INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.in(uniqueDates))
                    )
                    .groupBy(
                            INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE,
                            INCOME_EXPENSE_DETAIL.PAYMENT_TYPE,
                            INCOME_EXPENSE_DETAIL.TYPE
                    )
                    .fetchMaps();

            for (LocalDateTime uniqueDate : uniqueDates) {
                List<Map<String, Object>> dateItems = rawData.stream().filter(item -> item.get("incomeExpenseDate").equals(uniqueDate)).toList();

                BigDecimal cashIncome = calculateSummary(dateItems, PaymentTypeConstant.CASH, IncomeExpenseTypeConstant.INCOME);
                BigDecimal cashExpense = calculateSummary(dateItems, PaymentTypeConstant.CASH, IncomeExpenseTypeConstant.EXPENSE);
                BigDecimal creditCardIncome = calculateSummary(dateItems, PaymentTypeConstant.CREDIT_CARD, IncomeExpenseTypeConstant.INCOME);
                BigDecimal creditCardExpense = calculateSummary(dateItems, PaymentTypeConstant.CREDIT_CARD, IncomeExpenseTypeConstant.EXPENSE);

                result.add(Map.of(
                        "incomeExpenseDate", uniqueDate,
                        "cashIncome", cashIncome,
                        "cashExpense", cashExpense,
                        "cashBalance", cashIncome.add(cashExpense),
                        "creditCardIncome", creditCardIncome,
                        "creditCardExpense", creditCardExpense,
                        "creditCardBalance", creditCardIncome.add(creditCardExpense)
                ));
            }
        }

        return result;
    }

    /**
     * Queries income expense summary based one single day. Includes total income, total expense and balance.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @param dates:          The date list which stores each incomeExpenseDate might duplicate.
     * @return The list of map instance which contains keys below.
     * incomeExpenseDate: The date of income or expense happens. No duplicate date in this list.
     * totalIncome: Float of the total income in this day.
     * totalExpense: Float of the total expense in this day.
     * balance: Float of the balance in this day.
     */
    public List<Map<String, Object>> queryDailySummaryList(String accountGroupId, List<LocalDateTime> dates) {
        // Gets unique date from the list.
        Set<LocalDateTime> uniqueDates = new HashSet<>(dates);

        // Queries the summary based on these dates and returns the result.
        return queryDailySummaryList(accountGroupId, uniqueDates);
    }

    /**
     * Queries all distinct income/expense years.
     *
     * @param accountGroupId: The string of the user account group id.
     * @return The list of Integer of existing year.
     */
    public List<Integer> queryExistingDistinctYears(String accountGroupId) {
        return dslContext.selectDistinct(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE))
                .from(INCOME_EXPENSE_DETAIL)
                .where(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                .orderBy(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).desc())
                .fetchInto(Integer.class);
    }

    /**
     * Queries summary data of provided year.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param year:           The int of year whose summary data will be retrieved.
     * @return The map which contains return result of querySummaryByYears method.
     */
    public Map<String, Object> queryAnnualSummary(String accountGroupId, int year) {
        List<Map<String, Object>> result = queryAnnualSummaryByYears(accountGroupId, List.of(year));

        return result != null && !result.isEmpty() ? result.getFirst() : null;
    }

    /**
     * Queries the summary based on the type and year for each month.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param type:           The string of the income/expense type.
     * @param paymentType:    The string of the payment type.
     * @param year:           The Integer of year.
     * @return The map instance which contains keys below.
     * (year): The double of the total amount of this year.
     * (month): The double of the total amount of this month.
     */
    public Map<String, Object> queryAnnualMonthlySummary(String accountGroupId, String type, String paymentType, Integer year) {
        // LinkedHashMap preserves the insertion order of the keys.
        Map<String, Object> result = new LinkedHashMap<>();

        BigDecimal totalAmount = BigDecimal.ZERO;

        // Queries all existing monthly amount data in this year.
        List<Map<String, Object>> existingMonthlyAmountList = dslContext.select(
                        DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).as("month"),
                        DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).as("amount")
                )
                .from(INCOME_EXPENSE_DETAIL)
                .join(INCOME_EXPENSE_DETAIL_TYPE).on(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(INCOME_EXPENSE_DETAIL_TYPE.NAME))
                .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID))
                .where(
                        INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue()
                                .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                                .and(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year))
                                .and(INCOME_EXPENSE_DETAIL.TYPE.eq(type))
                                .and(INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(paymentType))
                )
                .groupBy(DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE))
                .fetchMaps();

        DecimalFormat decimalFormat = new DecimalFormat("00");

        // Prepares the amount of 12 months based on existing monthly amount data.
        for (int i = 0; i < 12; i++) {
            int month = i + 1;

            // The amount of each month.
            BigDecimal amount = BigDecimal.ZERO;

            // Gets the monthly amount data from query result for current month.
            Map<String, Object> monthlyAmount = existingMonthlyAmountList.stream()
                    .filter(p -> Integer.parseInt(p.get("month").toString()) == month)
                    .findFirst()
                    .orElse(null);

            // If monthly data exists then updates amount.
            if (monthlyAmount != null) {
                amount = (BigDecimal) monthlyAmount.get("amount");
            }

            // Calculates the annual total amount.
            totalAmount = totalAmount.add(amount);

            // Formats the month to string. Example: 1 to 01.
            String monthString = decimalFormat.format(month);

            // Puts the amount into the map with month string as key.
            result.put(monthString, amount);
        }

        // Puts the total amount into the map with year string as key.
        result.put(year.toString(), totalAmount);

        return result;
    }

    /**
     * Queries the summary based on the type and month for each day.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param type:           The string of the income/expense type.
     * @param paymentType:    The string of the payment type.
     * @param year:           The Integer of year.
     * @return The map instance which contains keys below.
     * (year): The double of the total amount of this year.
     * (month): The double of the total amount of this month.
     */
    public Map<String, Object> queryMonthlyDailySummary(String accountGroupId, String type, String paymentType, Integer year, Integer month) {
        // LinkedHashMap preserves the insertion order of the keys.
        Map<String, Object> result = new LinkedHashMap<>();

        BigDecimal totalAmount = BigDecimal.ZERO;

        // Queries all existing monthly amount data in this year.
        List<Map<String, Object>> existingDailyAmountList = dslContext.select(
                        DSL.day(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).as("day"),
                        DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).as("amount")
                )
                .from(INCOME_EXPENSE_DETAIL)
                .join(INCOME_EXPENSE_DETAIL_TYPE).on(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(INCOME_EXPENSE_DETAIL_TYPE.NAME))
                .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID))
                .where(
                        INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue()
                                .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                                .and(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year))
                                .and(DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(month))
                                .and(INCOME_EXPENSE_DETAIL.TYPE.eq(type))
                                .and(INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(paymentType))
                )
                .groupBy(DSL.day(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE))
                .fetchMaps();

        int lastDay = DateHelper.getLastDayOfMonth(year, month).getDayOfMonth();
        DecimalFormat decimalFormat = new DecimalFormat("00");

        // Prepares the amount of days based on existing daily amount data.
        for (int i = 0; i < lastDay; i++) {
            // Index is 0 based.
            int day = i + 1;

            // The amount of each day.
            BigDecimal amount = BigDecimal.ZERO;

            // Gets the daily amount data from query result for current day.
            Map<String, Object> dailyAmount = existingDailyAmountList.stream()
                    .filter(p -> Integer.parseInt(p.get("day").toString()) == day)
                    .findFirst()
                    .orElse(null);

            // If daily data exists then updates amount.
            if (dailyAmount != null) {
                amount = (BigDecimal) dailyAmount.get("amount");
            }

            // Calculates the annual total amount.
            totalAmount = totalAmount.add(amount);

            // Formats the month to string. Example: 1 to 01.
            String dayString = decimalFormat.format(day);

            // Puts the amount into the map with month string as key.
            result.put(dayString, amount);
        }

        // Puts the total amount into the map with year string as key.
        result.put(String.format("%s%s", year, month), totalAmount);

        return result;
    }

    /**
     * Queries the top bank top order by amount based on type and payment type in particular year and month.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param pageSize:       How many records is return in one trip.
     * @param pageIndex:      Where to take rows.
     * @param type:           The string of income/expense type.
     * @param paymentType:    The string of payment type.
     * @param year:           The Integer of year.
     * @param month:          The Integer of month. It can be null.
     * @return The list of map instance which contains keys below.
     * id: String of the income expense detail id.
     * incomeExpenseDate: Date of the income expense detail.
     * amount: Float of the income expense detail amount.
     * type: String of the type. It's income or expense.
     * detailType: String of the detail type which indicates where the income from or the expense goes.
     * paymentType: String of the payment type which how the expense payed.
     * bankId: String of the band id of the bank if the expense is payed by card.
     * bankShortName: String of the short name of the bank if the expense is payed by card.
     * bankLogoUrl: String of the logo url of the bank if the expense is payed by card.
     * bankCardId: String of the id of the bank card if the expense is payed by card.
     * bankCardName: String of the name of the bank card if the expense is payed by card.
     * bankCardIsCreditCard: Bool of if this bank card is credit card.
     * details: String of the income or expense detail.
     */
    public List<Map<String, Object>> queryTopRankByAccountGroup(String accountGroupId, Integer pageSize, Integer pageIndex, String type, String paymentType, Integer year, Integer month) {
        return dslContext.select(
                        INCOME_EXPENSE_DETAIL.ID.as("id"),
                        INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.as("incomeExpenseDate"),
                        INCOME_EXPENSE_DETAIL.AMOUNT.as("amount"),
                        INCOME_EXPENSE_DETAIL.TYPE.as("type"),
                        INCOME_EXPENSE_DETAIL.DETAIL_TYPE.as("detailType"),
                        INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.as("paymentType"),
                        BANK.ID.eq("bankId"),
                        BANK.SHORT_NAME.as("bankShortName"),
                        BANK_CARD.ID.as("bankCardId"),
                        BANK_CARD.NAME.as("bankCardName"),
                        BANK_CARD.IS_CREDIT_CARD.as("bankCardIsCreditCard"),
                        INCOME_EXPENSE_DETAIL.DETAILS.as("details")
                )
                .from(INCOME_EXPENSE_DETAIL)
                .join(INCOME_EXPENSE_DETAIL_TYPE).on(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(INCOME_EXPENSE_DETAIL_TYPE.NAME))
                .leftJoin(BANK_CARD).on(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
                .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
                .where(
                        INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue())
                                .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                                .and(INCOME_EXPENSE_DETAIL.TYPE.eq(type))
                                .and(
                                        SharedUtil.isNullOrEmpty(paymentType) ?
                                                DSL.trueCondition() :
                                                INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(paymentType)

                                )
                                .and(
                                        year == null ?
                                                DSL.trueCondition() :
                                                DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year)
                                )
                                .and(
                                        month == null ?
                                                DSL.trueCondition() :
                                                DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(month)
                                )
                )
                .orderBy(DSL.abs(INCOME_EXPENSE_DETAIL.AMOUNT).desc())
                .limit(pageSize)
                .offset(pageIndex * pageSize)
                .fetch()
                .map(item -> {
                    Map<String, Object> result = item.intoMap();
                    result.put("bankLogoUrl", bankService.getLogoUrl((String) item.get("bankShortName")));

                    return result;
                });
    }

    /**
     * Queries the expense summary based on the type and year.
     * It focuses on expense detail type and does not include the credit card repayment.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param paymentType:    The string of payment type.
     * @param year:           The Integer of year.
     * @param month:          The Integer of month. This can be null.
     * @return The list of map instance which contains keys below.
     * detailType: The sting of the detail type. The value is corresponding amount.
     * amount: The corresponding amount of this detail type.
     */
    public List<Map<String, Object>> queryExpenseByDetailType(String accountGroupId, String paymentType, Integer year, Integer month) {
        // Queries the detail type names.
        List<String> detailTypes = incomeExpenseDetailTypeService.queryNameForAnalysisByAccountGroup(accountGroupId, IncomeExpenseTypeConstant.EXPENSE);

        return querySummaryByDetailType(accountGroupId, paymentType, year, month, IncomeExpenseTypeConstant.EXPENSE, detailTypes);
    }

    /**
     * Queries the income summary based on the type and year.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param paymentType:    The string of payment type.
     * @param year:           The Integer of year.
     * @param month:          The Integer of month. This can be null.
     * @return The list of map instance which contains keys below.
     * detailType: The sting of the detail type. The value is corresponding amount.
     * amount: The corresponding amount of this detail type.
     */
    public List<Map<String, Object>> queryIncomeByDetailType(String accountGroupId, String paymentType, Integer year, Integer month) {
        // Queries the detail type names.
        List<String> detailTypes = incomeExpenseDetailTypeService.queryNameForAnalysisByAccountGroup(accountGroupId, IncomeExpenseTypeConstant.INCOME);

        return querySummaryByDetailType(accountGroupId, paymentType, year, month, IncomeExpenseTypeConstant.INCOME, detailTypes);
    }

    /**
     * Queries the overall summary for account group.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @return The list of map instance which contains keys below.
     * cashIncome: The total cash income amount of overall.
     * cashExpense: The total cash expense amount of overall.
     * cashBalance: The total cash balance amount of overall.
     * creditCardIncome: The total credit card income amount of overall.
     * creditCardExpense: The total credit card expense amount of overall.
     * creditCardBalance: The total credit card balance amount of overall.
     */
    public Map<String, Map<String, Object>> queryOverallSummary(String accountGroupId) {
        Map<String, Map<String, Object>> result = new HashMap<>();
        result.put("cashIncomeSummary", new HashMap<>());
        result.put("cashExpenseSummary", new HashMap<>());
        result.put("creditCardIncomeSummary", new HashMap<>());
        result.put("creditCardExpenseSummary", new HashMap<>());

        // Queries all SUM data for each year.
        // By now IncomeExpenseDetail and IncomeExpenseDetailType have no reference field. That's means no foreign key.
        // That's caused join operation takes 250+ms but sub-query takes 10+ms.
        List<Map<String, Object>> overallSummary = dslContext.select(
                        DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).as("year"),
                        INCOME_EXPENSE_DETAIL.TYPE.as("type"),
                        INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.as("paymentType"),
                        DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).as("amount")
                )
                .from(INCOME_EXPENSE_DETAIL)
                .where(
                        INCOME_EXPENSE_DETAIL.DETAIL_TYPE.in(buildAnalysisIncludedIncomeExpenseDetailTypeQuery(accountGroupId))
                                .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                )
                .groupBy(
                        DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE),
                        INCOME_EXPENSE_DETAIL.TYPE,
                        INCOME_EXPENSE_DETAIL.PAYMENT_TYPE
                )
                .fetchMaps();

        List<Integer> distinctYears = overallSummary.stream()
                .map(item -> Integer.parseInt(item.get("year").toString()))
                .distinct()
                .toList();

        BigDecimal cashIncomeSummary = BigDecimal.ZERO;
        BigDecimal totalCashIncomeSummary = BigDecimal.ZERO;
        BigDecimal cashExpenseSummary = BigDecimal.ZERO;
        BigDecimal totalCashExpenseSummary = BigDecimal.ZERO;
        BigDecimal creditCardIncomeSummary = BigDecimal.ZERO;
        BigDecimal totalCreditCardIncomeSummary = BigDecimal.ZERO;
        BigDecimal creditCardExpenseSummary = BigDecimal.ZERO;
        BigDecimal totalCreditCardExpenseSummary = BigDecimal.ZERO;

        for (Integer year : distinctYears) {
            cashIncomeSummary = calculateSummary(overallSummary, year, PaymentTypeConstant.CASH, IncomeExpenseTypeConstant.INCOME);
            totalCashIncomeSummary = totalCashIncomeSummary.add(cashIncomeSummary);
            result.get("cashIncomeSummary").put(year.toString(), cashIncomeSummary);

            cashExpenseSummary = calculateSummary(overallSummary, year, PaymentTypeConstant.CASH, IncomeExpenseTypeConstant.EXPENSE);
            totalCashExpenseSummary = totalCashExpenseSummary.add(cashExpenseSummary);
            result.get("cashExpenseSummary").put(year.toString(), cashExpenseSummary);

            creditCardIncomeSummary = calculateSummary(overallSummary, year, PaymentTypeConstant.CREDIT_CARD, IncomeExpenseTypeConstant.INCOME);
            totalCreditCardIncomeSummary = totalCreditCardIncomeSummary.add(creditCardIncomeSummary);
            result.get("creditCardIncomeSummary").put(year.toString(), creditCardIncomeSummary);

            creditCardExpenseSummary = calculateSummary(overallSummary, year, PaymentTypeConstant.CREDIT_CARD, IncomeExpenseTypeConstant.EXPENSE);
            totalCreditCardExpenseSummary = totalCreditCardExpenseSummary.add(creditCardExpenseSummary);
            result.get("creditCardExpenseSummary").put(year.toString(), creditCardExpenseSummary);
        }

        result.get("cashIncomeSummary").put("total", totalCashIncomeSummary);
        result.get("cashExpenseSummary").put("total", totalCashExpenseSummary);
        result.get("creditCardIncomeSummary").put("total", totalCreditCardIncomeSummary);
        result.get("creditCardExpenseSummary").put("total", totalCreditCardExpenseSummary);

        return result;
    }

    /**
     * Queries the annual summary in the specific years.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @param years:          The Integer of the year.
     * @return The list of map instance which contains keys below.
     * year: The year of these summary.
     * cashIncome: The total cash income amount of this year.
     * cashExpense: The total cash expense amount of this year.
     * cashBalance: The total cash balance amount of this year.
     * creditCardIncome: The total credit card income amount of this year.
     * creditCardExpense: The total credit card expense amount of this year.
     * creditCardBalance: The total credit card balance amount of this year.
     */
    public List<Map<String, Object>> queryAnnualSummaryByYears(String accountGroupId, List<Integer> years) {
        List<Map<String, Object>> result = new ArrayList<>();

        if (years != null && !years.isEmpty()) {
            // Queries all SUM data for each year.
            // By now IncomeExpenseDetail and IncomeExpenseDetailType have no reference field. That's means no foreign key.
            // That's caused join operation takes 250+ms but sub-query takes 10+ms.
            List<Map<String, Object>> summaryList = dslContext.select(
                            DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).as("year"),
                            INCOME_EXPENSE_DETAIL.TYPE.as("type"),
                            INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.as("paymentType"),
                            DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).as("amount")
                    )
                    .from(INCOME_EXPENSE_DETAIL)
                    .where(
                            INCOME_EXPENSE_DETAIL.DETAIL_TYPE.in(buildAnalysisIncludedIncomeExpenseDetailTypeQuery(accountGroupId))
                                    .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                                    .and(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).in(years))
                    )
                    .groupBy(
                            DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE),
                            INCOME_EXPENSE_DETAIL.TYPE,
                            INCOME_EXPENSE_DETAIL.PAYMENT_TYPE
                    )
                    .fetchMaps();

            BigDecimal totalCashIncome = BigDecimal.ZERO;
            BigDecimal totalCashExpense = BigDecimal.ZERO;
            BigDecimal totalCreditCardIncome = BigDecimal.ZERO;
            BigDecimal totalCreditCardExpense = BigDecimal.ZERO;

            for (int year : years) {
                BigDecimal cashIncome = calculateSummary(summaryList, year, PaymentTypeConstant.CASH, IncomeExpenseTypeConstant.INCOME);
                BigDecimal cashExpense = calculateSummary(summaryList, year, PaymentTypeConstant.CASH, IncomeExpenseTypeConstant.EXPENSE);
                BigDecimal creditCardIncome = calculateSummary(summaryList, year, PaymentTypeConstant.CREDIT_CARD, IncomeExpenseTypeConstant.INCOME);
                BigDecimal creditCardExpense = calculateSummary(summaryList, year, PaymentTypeConstant.CREDIT_CARD, IncomeExpenseTypeConstant.EXPENSE);
                totalCashIncome = totalCashIncome.add(cashIncome);
                totalCashExpense = totalCashExpense.add(cashExpense);
                totalCreditCardIncome = totalCreditCardIncome.add(creditCardIncome);
                totalCreditCardExpense = totalCreditCardExpense.add(creditCardExpense);

                result.add(Map.of(
                        "year", year,
                        "cashIncome", cashIncome,
                        "cashExpense", cashExpense,
                        "cashBalance", cashIncome.add(cashExpense),
                        "creditCardIncome", creditCardIncome,
                        "creditCardExpense", creditCardExpense,
                        "creditCardBalance", creditCardIncome.add(creditCardExpense)
                ));
            }

            result.add(0, Map.of(
                    "year", String.format("%s - %s", years.getLast(), years.getFirst()),
                    "cashIncome", totalCashIncome,
                    "cashExpense", totalCashExpense,
                    "cashBalance", totalCashIncome.add(totalCashExpense),
                    "creditCardIncome", totalCreditCardIncome,
                    "creditCardExpense", totalCreditCardExpense,
                    "creditCardBalance", totalCreditCardIncome.add(totalCreditCardExpense)
            ));
        }

        return result;
    }

    private BigDecimal calculateSummary(List<Map<String, Object>> result, Integer year, String paymentType, String type) {
        return result.stream()
                .filter(item -> (year == null || Integer.parseInt(item.get("year").toString()) == year)
                        && item.get("paymentType").equals(paymentType)
                        && item.get("type").equals(type))
                .map(item -> (BigDecimal) item.get("amount"))
                .reduce(BigDecimal.ZERO, BigDecimal::add);
    }

    private BigDecimal calculateSummary(List<Map<String, Object>> result, String paymentType, String type) {
        return calculateSummary(result, null, paymentType, type);
    }

    private SelectConditionStep buildAnalysisIncludedIncomeExpenseDetailTypeQuery(String accountGroupId) {
        return dslContext.select(INCOME_EXPENSE_DETAIL_TYPE.NAME)
                .from(INCOME_EXPENSE_DETAIL_TYPE)
                .where(
                        INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue())
                );
    }

    /**
     * Queries the summary based on the year and month, type and detail types.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @param paymentType:    The string of payment type.
     * @param year:           The Integer of year.
     * @param month:          The Integer of month.
     * @param type:           The string of IncomeExpenseTypeConstant.
     * @return The list of map instance which contains keys below.
     * detailType: The sting of the detail type. The value is corresponding amount.
     * amount: The corresponding amount of this detail type.
     * @detailTypes: The list string of ExpenseTypeConstant or IncomeTypeConstant.
     */
    private List<Map<String, Object>> querySummaryByDetailType(String accountGroupId, String paymentType, Integer year, Integer month, String type, List<String> detailTypes) {
        List<Map<String, Object>> result = new ArrayList<>();

        if (detailTypes != null && !detailTypes.isEmpty()) {
            result = dslContext.select(
                            DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).as("amount"),
                            INCOME_EXPENSE_DETAIL.DETAIL_TYPE.as("detailType")
                    )
                    .from(INCOME_EXPENSE_DETAIL)
                    .where(
                            INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                    .and(INCOME_EXPENSE_DETAIL.TYPE.eq(type))
                                    .and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.in(detailTypes))
                                    .and(
                                            SharedUtil.isNullOrEmpty(paymentType) ?
                                                    DSL.trueCondition() :
                                                    INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(paymentType)

                                    )
                                    .and(
                                            year == null ?
                                                    DSL.trueCondition() :
                                                    DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year)
                                    )
                                    .and(
                                            month == null ?
                                                    DSL.trueCondition() :
                                                    DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(month)
                                    )
                    )
                    .groupBy(INCOME_EXPENSE_DETAIL.DETAIL_TYPE)
                    .orderBy(DSL.abs(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT)).desc())
                    .fetchMaps();
        }

        return result;
    }
}
